<?php
/**
 * Author  : DAMMAK Wassim
 * Contact : wdammak@talend.com
 * Date    : 12/2008
 * @copyright talend.com 2008
 * @license GNU/GPL Version 2
 **/  
 
//if(empty($_SESSION)) die('no permissions for access');			 
	 
function php2js( $php_array, $js_array_name ) {
	// contrle des parametres d'entre
	if( !is_array( $php_array ) ) {
		//trigger_error( "php2js() => 'array' attendu en parametre 1, \n'".gettype($array)."' fourni !?!");
		return false;
	}
	if( !is_string( $js_array_name ) ) {
		//trigger_error( "php2js() => 'string' attendu en parametre 2, \n'".gettype($array)."' fourni !?!");
		return false;
	}

	// Cration du tableau en JS
	$script_js = "var $js_array_name = new Array();\n";
	
	// on rempli le tableau JS  partir des valeurs de son homologue PHP
	foreach( $php_array as $key => $value ) {
		if(!is_array($value)){		
			$value=str_replace("\r\n","",$value);
			$value=str_replace("\n\r","",$value);
			$value=str_replace("\n","",$value);
			$value=str_replace("\r","",$value);			
			$value=str_replace("","\\n",$value);
			$value=str_replace('&#x',"%u",$value);
			//$value=str_replace('%u',"%u",$value);
		}
		// pouf, on tombe sur une dimension supplementaire
		if( is_array($value) ) {
			// On va demander la cration d'un tableau JS temporaire
			$temp = uniqid('temp_'); // on lui choisi un nom bien barbare
			$t = php2js( $value, $temp ); // et on creer le script JS
			// En cas d'erreur, remonter l'info aux rcursions suprieures
			if( $t===false ) return false;

			// Ajout du script de cration du tableau JS temporaire
			$script_js.= $t;
			// puis on applique ce tableau temporaire  celui en cours de construction
			$script_js.= "{$js_array_name}['{$key}'] = {$temp};\n";
		}
		
		// Si la clef est un entier, pas de guillemets
		elseif( is_int($key) )  $script_js.= "{$js_array_name}[{$key}] = '{$value}';\n";
		
		// sinon avec les guillemets
		else 
			$script_js.= "{$js_array_name}['{$key}'] = '{$value}';\n";
	}

	// Et retourn le script JS
	return $script_js;
}

function arrayUnique($myArray)
{
    if(!is_array($myArray))
           return $myArray;

    foreach ($myArray as &$myvalue){
        $myvalue=serialize($myvalue);
    }

    $myArray=array_unique($myArray);

    foreach ($myArray as &$myvalue){
        $myvalue=unserialize($myvalue);
    }

    return $myArray;

} 

function mySerialize($array, $key){
	$string="";
	foreach($array as $itm)
		$string.=$itm->$key.",";
		
	if(substr($string,-1)==",")
		$string=substr($string,0,-1);
	return $string;
}

/**
 * Sorts an array according to a specified column
 * Params : array  $table
 *          string $colname
 *          bool   $numeric
 **/
function sort_col($table, $colname, $order=SORT_ASC) {
  $tn = $ts = $temp_num = $temp_str = array();
  foreach ($table as $key => $row) {
  	$row =array_flatten_recursive($row);
    if(is_numeric(substr($row[$colname], 0, 1))) {
      $tn[$key] = $row[$colname];
      $temp_num[$key] = $row;
	  $temp_num[$key]['key']=$key;
    }
    else {
      $ts[$key] = $row[$colname];
      $temp_str[$key] = $row;
	  $temp_str[$key]['key']=$key;
    }
  }
  unset($table);

  array_multisort($tn, $order, SORT_NUMERIC, $temp_num);
  array_multisort($ts, $order, SORT_STRING, $temp_str);
  
  return array_merge($temp_num, $temp_str);
}

function array_flatten_recursive($array,$std=true) {
	if($array) {
		$flat = array();
		foreach(new RecursiveIteratorIterator(new RecursiveArrayIterator($array), RecursiveIteratorIterator::SELF_FIRST) as $key=>$value) {
			if(!is_array($value)) {
				if($std==true){
					$flat[$key] = $value;
				}else{
					$flat[] = $value;
				}
			}
		}
 
		return $flat;
	} else {
		return false;
	}
}

//Get langue browser
if(isset($_SERVER['HTTP_ACCEPT_LANGUAGE']) && !isset($_SESSION['ln_browser']) ){
	$Langue_str = explode(',',$_SERVER['HTTP_ACCEPT_LANGUAGE']);
	$Langue_brw = strtolower(substr(chop($Langue_str[0]),0,2));
	//$full_Langue_name=strtolower(substr(chop($Langue_str[0]),0,5));
}

if(!isset($_SESSION['groupe'])){
	$first=true;
}

//Get Groupe
	if(!isset($_SESSION['gid'])){
		$groupe="guest";
		$select = $db->select()->from('users',array('gid','active'))->where('email = ?', $auth->getIdentity());
		$result = $db->fetchAll($select);
		$_SESSION['gid']=$result[0]->gid;
				
		if(isset($result) && count($result) > 0 && $result[0]->active==1) {				
			switch($result[0]->gid) {
				case 0 :
					$groupe="guest";
					break;
				case 1: 
					$groupe="editor";
					break;
				case 2: 
					$groupe="staff";
					break;
				case 3: 
					$groupe="admin";
					break;
			}			
		}elseif($result[0]->active==0){
			echo "<style class='noactived'>Your account is not activated!</span><br />";
		}
		$_SESSION['groupe']=$groupe;
		$_SESSION['activeUser']=$result[0]->active;
	}

	/** Instance pour la gestion d'accs **/
	$acl = new MyAcl($auth);		

	/** Interroger les ACL **/

	/*	echo $acl->isAllowed($groupe, null, 'voir') ?
			 "autoris<br/>" : "refus<br/>"; // autoris
		
		echo $acl->isAllowed($groupe, null, 'publier') ?
			 "autoris<br/>" : "refus<br/>"; // refus
		
		echo $acl->isAllowed($groupe, null, 'relire') ?
			 "autoris<br/>" : "refus<br/>"; // autoris
		
		echo $acl->isAllowed($groupe, null, 'voir') ?
			 "autoris<br/>" : "refus<br/>"; // autoris parce que hrit de Invit
		
		echo $acl->isAllowed($groupe, null, 'modifier') ?
			 "autoris<br/>" : "refus<br/>"; // refus parce qu'il n'y a pas de rgle pour 'modifier'
		
		echo $acl->isAllowed($groupe, null, 'voir') ?
			 "autoris<br/>" : "refus<br/>"; // autoris car administrateur est autoris pour tout
		
		echo $acl->isAllowed($groupe) ?
			 "autoris<br/>" : "refus<br/>"; // autoris car administrateur est autoris pour tout
		
		echo $acl->isAllowed($groupe, null, 'modifier') ?
			 "autoris<br/>" : "refus<br/>"; // autoris car administrateur est autoris pour tout*/
			 
			 
/*************************************************
//Set Ajax email User
*************************************************/
	echo "<script> var my_email='".$auth ->getIdentity ()."';</script>";

/*************************************************
//Get Id User
*************************************************/
	$selectUser = $db->select()->from('users','id_user')
					 ->where('email= ? ',$auth ->getIdentity ());

	$resultUser = $db->fetchAll($selectUser);
	if(!isset($_SESSION['id_user'])){
		$_SESSION['id_user'] = new stdClass; 
	}
	if(count($resultUser)>0){
		$_SESSION['id_user']=current($resultUser);
	}else{
		$_SESSION['id_user']->id_user=1;
	}

/*************************************************
//Get User translated word
*************************************************/
	//Query Translated						
	$resultTranslated = $db->fetchAll("SELECT `id_lang`, count(`id_key`) as nbr 
							FROM `translation`
							WHERE `id_user_trans`='".$_SESSION['id_user']->id_user."' 
							GROUP BY `id_lang`");

	//Query Validated
	$resultValidated = $db->fetchAll("SELECT `id_lang`, count(`id_key`) as nbr 
							FROM `translation`
							WHERE `id_user_validate`='".$_SESSION['id_user']->id_user."' 
							GROUP BY `id_lang`");
	
	//Make list of Short_name languages
	$arrLanguesShortName=array();
	
	$classesFlags='<style type="text/css">';
	foreach($resultLangues as $langue){
		$arrLanguesShortName[$langue->id_lang]=$langue->short_name;
		$classesFlags.= ".".$langue->short_name."{background:url(".$langue->flag.") no-repeat right; padding-right:16px;}";
	}
	$classesFlags.='</style>';

	//OutPut Sentences
	function echoSentences($arr){
		global $arrLanguesShortName;
		$str_sent='';
		foreach($arr as $translated){
			if($translated->nbr>0){
				$str_sent.='<option class="'.$arrLanguesShortName[$translated->id_lang].'">'.$translated->nbr."</option>";				
			}
		}
		return $str_sent;
	}
	
	

/*************************************************
//Get Selected Langue
*************************************************/	
	$nbrDefaultSelectLangue=1;
	if(isset($_SESSION['refLang'])){
		$first=false; 
	}else{
		$first=true;
	}
	
	$i=0;
	foreach($resultLangues as $langue){
		if($first==true){ //First execution
			$_SESSION['langues'][$langue->short_name] = new stdClass; 
			$_SESSION['langues'][$langue->short_name] = $langue;
			if($i<$nbrDefaultSelectLangue){
				$_SESSION['langues'][$langue->short_name]->isSelect = true;
			}else{
				$_SESSION['langues'][$langue->short_name]->isSelect = false;
			}
		}
		$i++;
		
		if(isset($_POST['lang'])){
			foreach($resultLangues as $langue){
				$is_sel=false;
				if(is_array($_POST['lang'])){
					foreach($_POST['lang'] as $post_lang){
						if($post_lang==$langue->short_name){
							$_SESSION['langues'][$langue->short_name]->isSelect=true;
							$is_sel=true;
							break;
						}
					}
				}else{
					$is_sel=true;
					$_SESSION['langues'][$langue->short_name]->isSelect=true;
					break;
				}
				
				if($is_sel==false){
					$_SESSION['langues'][$langue->short_name]->isSelect=false;
				}
			}
		}
	}

	//Add ln_browser => firstime
	if(!isset($_SESSION['ln_browser'])){
		foreach($_SESSION['langues'] as $langue){
			if(strtoupper($langue->short_name)==strtoupper($Langue_brw)){
				$_SESSION['langues'][$langue->short_name]->isSelect=true;
				$_SESSION['ln_browser']=$Langue_brw;
				$_SESSION['browser']=$langue;
			}
		}
	}


/*************************************************
//Get Reference and Target Langue
*************************************************/
	$i=0;
	foreach($resultLangues as $langue){							
		if($i==0){
			$_SESSION['refLang']= new stdClass;
			$_SESSION['refLang']= $langue;//Default=US
			
			$_SESSION['refTag']= new stdClass;
			$_SESSION['refTag']= $langue;//Default=US
		}
		if(isset($_POST['lang']) && is_array($_POST['lang'])){
			$j=0;
			foreach($_POST['lang'] as $ln){
				if($j==1 && $langue->short_name==$ln){
					$_SESSION['refTag']= $langue;
					break;
				}
				$j++;
			}
		}
		$i++;
	}
	
	if($first==true && isset($_SESSION['ln_browser']) && $_SESSION['browser']->id_lang!=$_SESSION['refTag']->id_lang){
		$_SESSION['refTag']=$_SESSION['browser'];
	}

/*************************************************
//Get langues selected: for query
*************************************************/
	if($_SESSION['refLang']->id_lang != $_SESSION['refTag']->id_lang){
		$sel_lang=$_SESSION['refLang']->id_lang.",".$_SESSION['refTag']->id_lang;
		$tab_langues_view_status=array($_SESSION['refLang'],$_SESSION['refTag']); 
		$nbr_langues=2;	
	}else{
		$sel_lang=$_SESSION['refLang']->id_lang;
		$tab_langues_view_status=array($_SESSION['refLang']);
		$nbr_langues=1;	
	}
										
	foreach($_SESSION['langues'] as $ln){
		if( $ln->isSelect==true && 
			$ln->id_lang != $_SESSION['refLang']->id_lang && 
			$ln->id_lang != $_SESSION['refTag']->id_lang 
		  )
		{ 
			$sel_lang.=",".$ln->id_lang;
			$tab_langues_view_status[]=$ln;
			$nbr_langues++;
		}
	}
		
/*************************************************
//Get Search Word
*************************************************/
	$searchWord='';
	if(isset($_POST['search_key']) && $_POST['search_key']!=''){
		$searchWord=$_POST['search_key'];
	}
		
/*************************************************
//Get Search By Date //Only for staff
*************************************************/
	$searchBeginDate='';
	if(isset($_POST['begindate']) && $_POST['begindate']!=''){
		$searchBeginDate=$_POST['begindate'];
	}
		
	$searchEndDate='';
	if(isset($_POST['enddate']) && $_POST['enddate']!=''){
		$searchEndDate=$_POST['enddate'];
	}

/*************************************************
//Get Selected Show 
*************************************************/	
	if($first==true && $_SESSION['gid']==0){
		$_SESSION['selectShow']="empty";
	}elseif($first==true && $_SESSION['gid']>1 ){
		$_SESSION['selectShow']="translated"; 
	}elseif($first==true){
		$_SESSION['selectShow']="all";
	}
	
	$is_first=$_SESSION['selectShow'];
	if($_SERVER['REQUEST_METHOD'] && isset($_POST['selectShow'])){
		$_SESSION['selectShow']=$_POST['selectShow'];
	}
		
	if($_SESSION['selectShow']=="by_author" && isset($_POST['translated_by_user']) && $_POST['translated_by_user']!=-1 ){
		$status_show="100";
	}elseif(isset($_POST['selected_file']) && $_SESSION['selectShow']=="order_by_files" && $_POST['selected_file']!=-1 ){
		$status_show="101";
	}else{
		if($_SESSION['selectShow']=="empty"){
			$status_show="0";
		}elseif($_SESSION['selectShow']=="translated" || $_SESSION['selectShow']=="order_by_date_desc"){
			$status_show="1";
		}elseif($_SESSION['selectShow']=="validated"){
			$status_show="2";
		}elseif($_SESSION['selectShow']=="my_translated_label"){
			$status_show="99";
		}else{
			$status_show="0,1,2";
		}
		
		$_POST['translated_by_user']=array();
		$_POST['selected_file']=array();
	}	
	
/*************************************************
//Get List of Users //Only for staff
*************************************************/

/*
if(($_SESSION['groupe']=="admin" || $_SESSION['groupe']=="editor" || $_SESSION['groupe']=="staff") && !isset($_SESSION['listUsers'])){
	if(isset($_POST['translated_by_user'])){
		$_SESSION['translated_by_user']=$_POST['translated_by_user'];
	}
	
	$selectListUsers = "SELECT t.`id_user_trans` as Id_User, 
							   t.`id_lang` as Langue,
							   count(t.`translation`) as Nbr_Translation
						FROM `translation` as t
						WHERE t.`id_status` IN (1,2) AND 
							  t.`id_user_trans` <> 1 AND 
							  t.`id_user_trans` <> 0
						GROUP BY t.`id_user_trans`,t.`id_lang` ";
	
	$resultListUsers = $db->fetchAll($selectListUsers);
	
	foreach($resultListUsers as $item_user){
		$arrUserResult[]=$item_user->Id_User;
	}
	$arrUserResult=array_unique($arrUserResult);

	$resultListUsersEmail = $db->fetchAll("
						SELECT u.`id_user`, u.`email` as Email
						FROM users u
						WHERE u.`id_user` IN (".implode(",",$arrUserResult).");" );
										
	$arrUsersEmail=array();
	foreach($resultListUsersEmail as $item){
		$arrUsersEmail[$item->id_user]=$item->Email;
	}

	foreach($resultListUsers as $item_user){
		if(	$current_user=="" || $current_user!=$item_user->Id_User){
			$current_user=$item_user->Id_User;
		}
		$arry_users_list[$current_user]['email']=$arrUsersEmail[$item_user->Id_User];
		$arry_users_list[$current_user]['total']+=intval($item_user->Nbr_Translation);	
		$arry_users_list[$current_user][$arrLanguesShortName[$item_user->Langue]]=$item_user->Nbr_Translation;
	}
	$resultListUsers=sort_col($arry_users_list, 'total', SORT_DESC);
	$_SESSION['listUsers']=$resultListUsers;
}
$resultListUsers=$_SESSION['listUsers'];
*/

/*************************************************
//Get Select Item 
*************************************************/	
	if(!isset($_SESSION['item_select']) || $_SESSION['item_select']<0 ){ 
		$_SESSION['item_select']=1;
	}
	
/*************************************************
//Get Show Keys/page 
*************************************************/	
	if(!isset($_POST['textShow']) && !isset($_SESSION['textShow'])){
		$_SESSION['textShow']=15;
	}
	if(isset($_POST['textShow'])){
		$_SESSION['textShow']= $_POST['textShow'];
	}

/*************************************************
//Get Number page selected
*************************************************/	
	if($is_first!="order_by_date_desc" && $_POST['selectShow']=='order_by_date_desc'){
		$_SESSION['num_page']=1;
		$_POST['selectPage']=1;
	}

	if($_SESSION['num_page']==0){
		$_SESSION['num_page']=1;
	}
	if(!isset($_SESSION['num_page']) && !isset($_POST['selectPage']) ){
		$_SESSION['num_page']=1;
	}elseif(isset($_POST['selectPage'])){
		$_SESSION['num_page']=$_POST['selectPage'];
	}

/*************************************************
//Get current project
*************************************************/	
	if(isset($_POST['project'])){
		$_SESSION['project']=$_POST['project'];
	}elseif(!isset($_SESSION['project'])){
		$_SESSION['project']=1;
	}

/*************************************************
//Get Files
*************************************************/
	$selectFiles = "SELECT f.`id_file`, f.`name`, f.`path`
					FROM `keys` as k
					LEFT JOIN `files` as f ON f.`id_file`=k.`id_file`
					WHERE k.`id_projet`=".$_SESSION['project']."
					GROUP BY k.`id_file`
					ORDER BY f.`path` ASC";

	$resultFiles = $db->fetchAll($selectFiles);

	
/*************************************************
//Get Count Keys in database
*************************************************/	

  //When Show = ( ALL ) - keys ref for all languages
	if( !isset($_SESSION['keys'][$_SESSION['project']]) || empty($_SESSION['keys'][$_SESSION['project']]) ){
		/** Select Only distinct keys **/
		$selectKeysNotDuplicate = "
				SELECT k.`id_key` 
				FROM `keys` k 
				LEFT JOIN `translation` as t ON k.`id_key`=t.`id_key`
				WHERE t.`id_lang` = 1 AND
					  k.`id_projet` = ".intval($_SESSION['project'])."
			";
			
		$resultKeysNotDuplicate = $db->fetchAll($selectKeysNotDuplicate); 	
		
		//serialize array['id_key']
		$KeysNotDuplicate=mySerialize($resultKeysNotDuplicate,'id_key');
		
		$_SESSION['keys'][$_SESSION['project']]= $KeysNotDuplicate;
	}
	
	$KeysNotDuplicate= $_SESSION['keys'][$_SESSION['project']];
	
  //End When Show = ( ALL ) - keys ref for all languages
  
  	
	$selectKeysNotDuplicate = "
			SELECT  k.`id_key` 
			FROM `translation` t
			LEFT JOIN `keys` as k ON t.`id_key`= k.`id_key` ";
			
	if($searchWord!=''){
		$selectKeysNotDuplicate .= "
			JOIN `users` as u ON t.`id_user_trans`=u.`id_user` ";
	}	
				
	$selectKeysNotDuplicate .= "
			WHERE k.`id_projet` = ".intval($_SESSION['project'])." AND ";
	
	$selectKeysNotDuplicateWithouLang=$selectKeysNotDuplicate ;
	
	$selectKeysNotDuplicate.="			
			  t.`id_lang`=".$_SESSION['refTag']->id_lang." AND ";
	
	
#####################
# Search
#####################	
	$exec=false;	
	
	//Search By keyword	
	$querySearch="";
	if($searchWord!=''){
		$exec=true;	
		
		switch($_POST['search_option']){
			case "exactly":
				$lf_str="";
				$rt_str="";
				break;
			case "begin":
				$lf_str="";
				$rt_str="%";
				break;			
			case "end":
				$lf_str="%";
				$rt_str="";
				break;				
			default:
				$lf_str="%";
				$rt_str="%";			
		}			
		
		if($_POST['search_option']=="bykey"){		  
			$querySearch="		
				
				 ( 					
					k.`key_name` LIKE '".$lf_str.addslashes($searchWord).$rt_str."' OR k.`id_key`='".addslashes($searchWord)."'
				) 
			 
			";
		}else{
			$querySearch="		
				
				 (
					t.`translation` LIKE '".$lf_str.addslashes($searchWord).$rt_str."' OR 
					u.`email` LIKE '".$lf_str.addslashes($searchWord).$rt_str."' OR 
					k.`key_name` LIKE '".$lf_str.addslashes($searchWord).$rt_str."' 
				) 
			 
			";
		}
		
		$selectKeysNotDuplicate=str_replace('t.`id_lang`='.$_SESSION['refTag']->id_lang,"t.`id_lang` IN (".$sel_lang.")",$selectKeysNotDuplicate);
	}
	
	//Search By date
	$querySearchByDate="";
	if($searchBeginDate!='' || $searchEndDate!=''){
		$exec=true;
		
		if(strtotime($searchBeginDate)>strtotime($searchEndDate)){
			$perm=$searchBeginDate;
			$searchBeginDate=$searchEndDate;
			$searchEndDate=$perm;
		}
							  
		if($searchBeginDate!=''){
			$querySearchByDate.="
				  t.`date` >= '".$searchBeginDate."' AND ";
		}
		
		if($searchEndDate!=''){
			$querySearchByDate.="
				  t.`date` <= '".$searchEndDate."' AND ";
		}
		
		if(substr($querySearchByDate, -4)=="AND "){
			$querySearchByDate=substr($querySearchByDate,0,-4);
		}
		
		$selectKeysNotDuplicate=str_replace('t.`id_lang`='.$_SESSION['refTag']->id_lang,"t.`id_lang` IN (".$sel_lang.")",$selectKeysNotDuplicate);
	}
		
		
	if($exec==true){	
		if($querySearch!='' && $querySearchByDate!=''){
			$querySearch.= ' AND '.$querySearchByDate;
		}elseif($querySearchByDate!=''){
			$querySearch= $querySearchByDate;
		}
		if($querySearch!=''){
			$querySearch=" AND ".$querySearch;
		}
	}
	
	
	
/**
 * When Show = ( Translated | Validated | Empty | Translated by me )
**/		
		
####################
# Select By Me 
####################
	if($status_show=="99" || $_POST['translaed_opt']=='by_me'){
		$selectKeysNotDuplicate .=" 
			  t.`id_status`=1 AND t.`id_user_trans`='".$_SESSION['id_user']->id_user."' ";
		if($_POST['translaed_opt']=='by_me')$selectKeysNotDuplicate .=" AND ";
	}
	
####################
# Select By User
####################
	if($status_show=="100"){	
		$selectKeysNotDuplicate .=" 
			  t.`id_user_trans`='".$_POST['translated_by_user']."' ";
		$selectKeysNotDuplicateWithouLang.=" 
			  t.`id_user_trans`='".$_POST['translated_by_user']."' ";
			  
	}
	
####################
# Select By File
####################
	if( $status_show=="101"){	
		$selectKeysNotDuplicate.="
			   k.`id_file`='".$_POST['selected_file']."' ";	
	} 
	 
	 
####################
# Add Filter Status
####################
	if(in_array($_POST['selectShow'],array('all','empty','translated','validated'))){
		$selectKeysNotDuplicate.=" 
			  t.`id_status` IN (".$status_show.") AND ";
	}
	

####################
# Add Filter Search
####################
	if(substr(trim(str_replace('\r','',trim($selectKeysNotDuplicate))), -3)=="AND"){
		$selectKeysNotDuplicate=trim(str_replace('\r','',trim($selectKeysNotDuplicate)));
		$selectKeysNotDuplicate =substr($selectKeysNotDuplicate,0,-3);
		$selectKeysNotDuplicate.=" 
			$querySearch
			";
	}else{
		$selectKeysNotDuplicate.=" 
			$querySearch
			";
	}
	
	//echo "<pre>".$selectKeysNotDuplicate."</pre>";

####################
# Grouper les translations vides si la langue != EN => Appliquer propagation
####################		
	if($status_show=="0" && $_SESSION['refTag']->id_lang!="1"){
		$arr_k=$db->fetchAll($selectKeysNotDuplicate);
		if(count($arr_k)>0){
			foreach($arr_k as $it){
				$arr_i[]= $it->id_key;				
			}
			$arr_i=implode(',',$arr_i);
			$selectKeysNotDuplicate="
				SELECT k.`id_key` 
				FROM `translation` t
				LEFT JOIN `keys` as k ON t.`id_key`= k.`id_key`
				WHERE t.`id_lang` = 1 AND
					  k.`id_projet` = ".intval($_SESSION['project'])." AND
					  k.`id_key` IN (".$arr_i.") 
					  GROUP BY t.`translation` ";
		}
	}
		
#####################
# Order By Date
#####################
	if($_POST['selectShow']=="order_by_date_desc"){
		$selectKeysNotDuplicate=trim($selectKeysNotDuplicate);
		if(substr(trim(str_replace('\r','',trim($selectKeysNotDuplicate))), -3)=="AND"){
			$selectKeysNotDuplicate=trim(str_replace('\r','',trim($selectKeysNotDuplicate)));
			$selectKeysNotDuplicate =substr($selectKeysNotDuplicate,0,-3);
		}
		$selectKeysNotDuplicate.='
			ORDER BY t.`date` DESC	';
	}
	
	
	
######################
# Execute Query with filter
#####################	
	//echo "<pre>$selectKeysNotDuplicate</pre>";
	
	$resultKeysNotDuplicate = $db->fetchAll($selectKeysNotDuplicate);
	
	$KeysNotDuplicate='';

	if(!empty($resultKeysNotDuplicate)){
		$KeysNotDuplicate=mySerialize($resultKeysNotDuplicate,'id_key');			
	}
	$resultCount=substr_count($KeysNotDuplicate,",");
	if($resultCount==0 && $KeysNotDuplicate!=""){
		$resultCount=1;
	}	
	 	   
	//By Status/Search OR ALL
	if(empty($KeysNotDuplicate) || $KeysNotDuplicate==""){	
		$wheres_all.=" AND k .`id_key` IN (0) ";
	}else{
		$wheres_all.=" AND t.`id_key` IN (".$KeysNotDuplicate.") ";
	}
 
 	//Get Ids US	
	$selectTransFinaleKeyIds="
		SELECT k.`id_key` 
		FROM `keys` k 
		JOIN `translation` as t ON t.`id_key` = k .`id_key`	
		WHERE k.`id_projet`='".intval($_SESSION['project'])."' 	
		AND t.`id_lang`=1
		$wheres_all	
		ORDER BY k.`key_name`			
		";

 
/*************************************************
//Get Number total pages 
*************************************************/
	$nbrPages= ceil($resultCount / $_SESSION['textShow']);	
	if($_SESSION['num_page']>$nbrPages){
		$_SESSION['num_page']=$nbrPages;
		$_SESSION['item_select']=($nbrPages-1)*$_SESSION['textShow']+1;
	}
	
	if($is_first!="order_by_date_desc" && $_POST['selectShow']=='order_by_date_desc'){
		$_SESSION['item_select']=1;
	}
	
/*************************************************
//Get Num pages selected after next/prev click 
*************************************************/		
	if(isset($_POST['first_x'])) $_SESSION['num_page']=1;
	if(isset($_POST['last_x'])) $_SESSION['num_page']=$nbrPages;
	if(isset($_POST['next_x']) && $_SESSION['num_page']<=$nbrPages)$_SESSION['num_page']=$_SESSION['num_page']+1;
	if(isset($_POST['next_x']) && $_SESSION['num_page']> $nbrPages) $_SESSION['num_page']=1;
	if(isset($_POST['prev_x']) && $_SESSION['num_page']>1) $_SESSION['num_page']=$_SESSION['num_page']-1;
	
	if($_SESSION['item_select']>($_SESSION['num_page'])*$_SESSION['textShow']){		
		$_SESSION['item_select']=($_SESSION['num_page']-1)*$_SESSION['textShow']+1;
	}elseif($_SESSION['item_select']<=($_SESSION['num_page']-1)*$_SESSION['textShow']){
		$_SESSION['item_select']=($_SESSION['num_page']-1)*$_SESSION['textShow']+1;
	}	
	
	if($_SESSION['item_select']<0){
		$_SESSION['item_select']=1;
	}
	
/*************************************************
//Get Selected item and Num page after : Go->Key->num click 
*************************************************/	
	if(isset($_POST['goKey']) || isset($_POST['goKeyNum'])){					
		if( $_POST['goKeyNum'] > 0 && $_POST['goKeyNum']<=$resultCount ) {
			$_SESSION['num_page']= ceil( $_POST['goKeyNum'] / $_SESSION['textShow'] );
			$_SESSION['item_select']= $_POST['goKeyNum'];
		}
	}
	
/*************************************************
//Get langues selected: for query
*************************************************/	
	$offset=($_SESSION['num_page']*$_SESSION['textShow'])-($_SESSION['textShow']);
	if($offset<0){
		$offset=0;
	}
	
	$selectTransFinaleKeyIds.="
			LIMIT ".$_SESSION['textShow']."			
			OFFSET ".$offset;	

	//Table qui contient tous les ids keys US
	$resultTransFinale = $db->fetchAll($selectTransFinaleKeyIds); 
	
	$arrDataUs=array();
	//Get Us Translation
	foreach($resultTransFinale as $it){
		$arrDataUs[]=$it->id_key;
	}
	
	if(count($arrDataUs)>0){
		//Final Query
		$selectTransFinaleNew ="
			SELECT k.`key_name`, k.`id_key`, k.`max_length`,
				   
				   t.`id_lang` , t.`translation`, t.`id_status`, t.`id_user_trans`,
				   
				   CONCAT('trunk','\\\',f.`path`,'\\\','<b>',f.`name`,'</b>{desc}',f.`desc`) as name, 
				   
				   u.`email`, e.`email` as email_editor,
				   
				   t.`date` as date_trans
			
			FROM `keys` k 
			JOIN `files` as f ON f.`id_file`=k.`id_file`
			JOIN `translation` as t ON t.`id_key` = k .`id_key`
			LEFT JOIN `users` as u ON u.`id_user` = t.`id_user_trans`
			LEFT JOIN `users` as e ON e.`id_user` = t.`id_editor`
			WHERE k.`id_key` IN (".implode(",",$arrDataUs).") 
			";
		
		$arrData=array();
		foreach($tab_langues_view_status as $ln){
			//Langue par langue
			$resultTransFinale = $db->fetchAll($selectTransFinaleNew." AND t.`id_lang`=".$ln->id_lang); 
			foreach($resultTransFinale as $it){
				$arrData[$ln->id_lang][]=$it;
			}
		}
		$arrDataFinale=array();
		foreach($arrDataUs as $it){
			foreach($tab_langues_view_status as $ln){
				foreach($arrData[$ln->id_lang] as $item){
					if($item->id_key==$it){
						$arrDataFinale[]=$item;
						break;
					}
				}
			}
		}	
	
		$resultTransFinale=$arrDataFinale;
		$TableAllWordsWithDetails= array();
		$id_tab_words=($_SESSION['num_page']-1)*$_SESSION['textShow']+1;
		$change_id="";
	
		foreach($resultTransFinale as $itm){
			if( $change_id!=$itm->id_key ){
				$change_id=$itm->id_key;
				$TableAllWordsWithDetails[$itm->id_key][0]=array($itm->key_name,$itm->max_length,addslashes($itm->name),$id_tab_words);			
				$id_tab_words++;
			}
			//$itm->translation=str_replace("\r\n","\\n",$itm->translation);
			$TableAllWordsWithDetails[$itm->id_key][$itm->id_lang]=array(addslashes($itm->translation),$itm->id_status,$itm->id_lang,$itm->id_user_trans,$itm->email,$itm->email_editor);		
		}

	//No results	
	}else{
		$TableAllWordsWithDetails=array();		
	}

	//Make table for javascript
	echo "<script type=\"text/javascript\">";
	echo php2js($TableAllWordsWithDetails,'arr');
	echo "</script>";
	
/*************************************************
//Get Ligne Selected 
*************************************************/
foreach($TableAllWordsWithDetails as $itm){
	if($_SESSION['item_select']==$itm[0][3] ){
		$line_selected=$itm;
		break;
	}
}

if(!(count($line_selected)>0) && count($TableAllWordsWithDetails)>0 ){	
	foreach($TableAllWordsWithDetails as $item){
		$_SESSION['item_select']= $item[0][3];
		$line_selected=$item;
		break;
	}
}

/*************************************************
//Get src logo status word
*************************************************/	
function getSrcLogoStatus($id_status){
	if($id_status==2){
		return  'img/accept.png';
	}elseif($id_status==1){
		return 'img/pencil.png';
	}elseif($id_status==0){
		return 'img/white.png';
	}else{
		return 'img/white.png';
	}
}	

/*************************************************
//Get src logo flag for langue
*************************************************/	
function getSrcFlag($id_lang){
	foreach($_SESSION['langues'] as $ln){
		if($ln->id_lang== $id_lang){
			return $ln->flag;
		}
	}
}

/*************************************************
//Get flag by status word
*************************************************/		
function getFlagStatus($id_status,$lang="",$type="",$index){
	return '<td><img id="img_status_tab_'.$index.'_'.$lang.'" src="'.getSrcLogoStatus($id_status).'" width="16" height="16" /></td>';
}	




/*************************************************
//Function: implode with quote
*************************************************/	
/*function myImplode($before, $after, $glue, $array){
    $nbItem = count($array);
    $i = 1;
    foreach($array as $item){
        if($i < $nbItem){
            $output .= "$before$item$after$glue";
        }else $output .= "$before$item$after";
        $i++;
    }
    return $output;
}*/

/*************************************************
//Function filterWord: remove chars specials
*************************************************/	
/*function filterWord($mot){	
	$mot=str_replace(',','',$mot);
	$mot=str_replace('.','',$mot);
	$mot=str_replace(':','',$mot);
	$mot=str_replace(';','',$mot);
	$mot=str_replace('(','',$mot);
	$mot=str_replace(')','',$mot);
	$mot=str_replace('->','',$mot);
	$mot=str_replace('\\','',$mot);	
	$mot=str_replace('{','',$mot);
	$mot=str_replace('}','',$mot);	
	$mot=str_replace('\\\'','',$mot);
	$mot=str_replace(",'',",'',$mot);	
	$mot=str_replace('\'','',$mot);
	$mot=str_replace('"','',$mot);
	$mot=trim($mot);
	return $mot;
}*/

/*************************************************
//Get Tab Tokens: All marge
*************************************************/
/*$i=0;
$change_id="";
foreach($resultTransFinale as $itm){
	if( $change_id!=$itm->id_key ){
		$change_id=$itm->id_key;		
		$var=filterWord($itm->translation);
		if(strlen($var)>1){	
			$piecesr[].=mb_strtoupper($var);
			$pieces = explode(" ", $var);
			foreach($pieces as $mot){
				$mot=filterWord($mot);		
				if(strlen($mot) > 1){
					$piecesr[].=mb_strtoupper($mot);
				}
			}
		}
	}
}

//Remove duplicate words
if(count($piecesr)>0)
array_unique($piecesr);

if(count($piecesr)>0){
	$mots=myImplode("'", "'", ",",$piecesr);

	$sql_token="SELECT * FROM `tokens` 
				WHERE UPPER(word) IN (".$mots.") AND 
					  id_lang IN (".$sel_lang.") 
				ORDER BY word ASC;";

	$resultTok = $db->fetchAll($sql_token); //Table qui contient tous les mots+status

	$k=0;
	if(count($resultTok)>0){
		foreach($resultTok as $tok){
			if($dif=="") $dif=$tok->word;
			if($dif==$tok->word){
				$resultTokens[$k][]= $tok;
			}else{			
				$k++;
				$resultTokens[$k][]= $tok;
				$dif=$tok->word;
			}
		}
	}
	
	//Make array js
	$i=0;
	if(count($resultTokens)>0){
		foreach($resultTokens as $toke){
			$j=0;
			foreach($toke as $tt){
				$tokenJs[$i][$j]=array($tt->word, $tt->translation,$tt->id_lang,$tt->id_token);
				$j++;
			}	
			$i++;
		}
	}	

	//Impr. table for javascript
	echo "<script type=\"text/javascript\">";
		echo php2js($tokenJs,'tok');
	echo "</script>";*/

	/*************************************************
	//Get Tab Tokens selected item
	*************************************************/
/*	if(isset($resultTokens) && count($resultTokens)>0){
		$i=0;
		foreach ($line_selected as $lin){
			if($i!=0 && $lin[1]!=""){
				$var=filterWord($lin[0]);
				if(strlen($var)>1){	
					$piecess[].=mb_strtoupper($var);
					$pieces = explode(" ", $var);
					foreach($pieces as $mot){
						$mot=filterWord($mot);
						if(strlen($mot)>1){
							$piecess[].=mb_strtoupper($mot);
						}
					}
				}
			}
			$i++;
		}
		
		array_unique($piecess);
		//Only for selected item
		if(count($piecess)>0 && count($resultTokens)>0){
			foreach($resultTokens as $toke){				
				foreach($piecess as $piece){
					foreach($toke as $tt){
						if((mb_strtoupper($tt->translation)==mb_strtoupper($piece) || 
							strpos(mb_strtoupper($tt->translation), mb_strtoupper($piece))!==false)){	
							$tokenActuel[]=$toke;
						}
					}
				}
			}
		}
	}
}

$tokenActuel=arrayUnique($tokenActuel);*/


//Get Header Module
function getHeaderMod($idMod=""){
	echo '<td class="box_module" valign="top" height="100%" '.$idMod.'>			
		   <table class="box_module_body" height="100%" cellpadding="0" cellspacing="0" width="100%">			
			 <tr> 			
			   <td valign="top">';
}

//Get Footer Module
function getFooterMod(){
	    echo '</td>
			</tr>
		  </table>		  
		</td>';
}

function getHeaderMod2($idMod=""){
	echo '<div class="box_module" height="100%" '.$idMod.'>			
		   <table class="box_module_body" height="100%" cellpadding="0" cellspacing="0" width="100%">			
			 <tr> 			
			   <td valign="top">';
}

//Get Footer Module
function getFooterMod2(){
	    echo '</td>
			</tr>
		  </table>		  
		</div>';
}
if($first==true)$first=false;

echo $classesFlags;
?>